#include "sqlite.h"

#include <QDebug>
#include <QRegularExpressionMatch>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>

Sqlite::Sqlite(const QString& dbfile) {
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName(dbfile);
    if (!db.open()) {
        qCritical() << "Cannot open DB:" << db.lastError().text();
    }

    QString createUserTable = R"(
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                email TEXT,
                age INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        )";
}

QString Sqlite::query(const QString& key) {
    QSqlQuery q("SELECT key, html FROM mdx;");
    while (q.next()) {
        QString k = q.value(0).toString();
        QString h = q.value(1).toString();
    }
    return "";
}

void Sqlite::execSql(const QString& sql, const QString& action) {
    QSqlQuery query(sql);
    if (!query.exec(sql)) {
        qDebug() << action << "失败：" << query.lastError().text();
    } else {
        qDebug() << action << "成功";
    }
}

void Sqlite::initCocaDB() {
    // 不存在创建表
    QString createUserTable = R"(
            CREATE TABLE IF NOT EXISTS mdx (
    entry TEXT NOT NULL,
                paraphrase TEXT NOT NULL ,
    rank INTEGER NOT NULL DEFAULT -1
            )
        )";
    creatTable(createUserTable);

    // 不存在rank，添加rank列
    bool hasRankColumn = false;
    QSqlQuery query("PRAGMA table_info(mdx)");
    while (query.next()) {
        QString columnName = query.value(1).toString();  // 第2列是字段名
        if (columnName == "rank") {
            hasRankColumn = true;
            break;
        }
    }
    qDebug() << hasRankColumn;
    if (!hasRankColumn) {
        QString sql = R"(
        ALTER TABLE mdx 
        ADD COLUMN rank INTEGER NOT NULL DEFAULT -1)";
        addCol(sql);
    }

    // 更新所有rank值
    QString html;
    QString queryHtml =
        R"(select entry,paraphrase,rank from mdx where rank = -1)";
    QSqlQuery select(queryHtml);

    QSqlQuery update;
    update.prepare(R"(update mdx set rank = :rank where entry = :entry)");
    QRegularExpression regex("<div class='rank'>(\\d+)</div>");
    QRegularExpressionMatch match;
    int rank = -1;
    QString key;
    int count = 0;

    while (select.next()) {
        key = select.value(0).toString();
        auto str = select.value(1).toString();
        int rank = select.value(2).toInt();
        if (rank == -1) {
            match = regex.match(str);
            if (match.hasMatch()) {
                rank = match.captured(1).toInt();
                update.bindValue(":rank", rank);
                update.bindValue(":entry", key);
                if (update.exec()) {
                    count++;
                    qDebug() << "update ok : " << key;
                } else
                    qDebug() << "更新rank失败: " << key;
            } else {
                qDebug() << "未找到匹配的rank数字";
            }
        }
    }
    qDebug() << "更新完成，共处理" << count << "条记录";
}
